import sqlite3
import SqlBase_v3_15 as SB
from datetime import datetime
import pytz
import os
import contextlib
import json
import shutil
from pathlib import Path
from werkzeug.utils import secure_filename

# 数据库和文件存储配置
DB_PATH = 'hr.db'  # 修改为根目录下的hr.db
EMPLOYEE_FILES_DIR = os.path.join('static', 'employees')
RESIGNED_EMPLOYEES_DIR = os.path.join('static', 'resigned_employees')  # 新增离职员工文件夹
ALLOWED_EXTENSIONS = {'png', 'jpg', 'jpeg', 'gif', 'pdf'}

def allowed_file(filename):
    """检查文件扩展名是否允许"""
    return '.' in filename and filename.rsplit('.', 1)[1].lower() in ALLOWED_EXTENSIONS

# 确保必要的目录存在
def ensure_directories():
    """确保所有必要的目录都存在"""
    directories = [
        EMPLOYEE_FILES_DIR,
        RESIGNED_EMPLOYEES_DIR  # 添加离职员工文件夹
    ]
    for directory in directories:
        if not os.path.exists(directory):
            os.makedirs(directory, exist_ok=True)

# 创建数据库连接的上下文管理器
@contextlib.contextmanager
def get_db_connection():
    ensure_directories()
    connection = sqlite3.connect(DB_PATH)
    db_client = SB.DBClient(mode='sqlite', connection=connection, debug=False)
    try:
        yield db_client
    finally:
        if connection:
            connection.commit()
            connection.close()

def sanitize_filename(filename):
    """安全地处理文件名，移除所有不安全的字符"""
    # 移除路径分隔符和特殊字符
    filename = os.path.basename(filename)
    # 只保留字母、数字、下划线、横线和点
    return ''.join(c for c in filename if c.isalnum() or c in '._-')

def get_employee_folder(employee):
    """
    根据员工信息创建并返回员工文件夹路径
    文件夹命名格式为：入职日期_姓名_职位
    """
    try:
        # 处理入职日期
        hire_date = employee.get('hire_date', '')
        if hire_date:
            try:
                # 直接使用 hire_date，因为它已经是 YYYY-MM-DD 格式
                date_str = hire_date.replace('-', '')  # 将 YYYY-MM-DD 转换为 YYYYMMDD
            except:
                date_str = 'unknown_date'
        else:
            date_str = 'unknown_date'

        # 安全地处理文件名组件
        name = sanitize_filename(employee.get('name', 'unknown'))
        position = sanitize_filename(employee.get('position', 'unknown'))
        
        # 创建文件夹名
        folder_name = f"{date_str}_{name}_{position}"
        folder_path = os.path.join(EMPLOYEE_FILES_DIR, folder_name)
        
        # 确保文件夹存在
        os.makedirs(folder_path, exist_ok=True)
        
        return folder_path
    except Exception as e:
        print(f"创建员工文件夹时出错: {e}")
        # 返回一个基于ID的后备文件夹路径
        backup_folder = os.path.join(EMPLOYEE_FILES_DIR, f"employee_{employee.get('id', 'unknown')}")
        os.makedirs(backup_folder, exist_ok=True)
        return backup_folder

def save_employee_file(employee_folder, file_obj, field_name):
    """安全地保存员工文件"""
    try:
        if file_obj and hasattr(file_obj, 'filename') and file_obj.filename:
            if not allowed_file(file_obj.filename):
                raise ValueError("不支持的文件类型")
            
            # 安全处理文件名
            original_filename = secure_filename(file_obj.filename)
            # 确保文件名唯一
            filename = f"{field_name}_{int(datetime.now().timestamp())}_{original_filename}"
            
            # 确保目标文件夹存在
            os.makedirs(employee_folder, exist_ok=True)
            
            # 构建完整的文件路径（使用正确的路径分隔符）
            file_path = os.path.join(employee_folder, filename)
            
            # 如果文件已存在，先删除旧文件
            if os.path.exists(file_path):
                os.remove(file_path)
            
            print(f"保存文件到: {file_path}")  # 调试信息
            # 保存文件
            file_obj.save(file_path)
            
            # 返回数据库中存储的相对路径（使用正斜杠作为分隔符）
            rel_path = os.path.join('employees', os.path.basename(employee_folder), filename).replace('\\', '/')
            return f"/static/{rel_path}"
    except Exception as e:
        print(f"保存文件时出错: {e}")
        return None

def delete_employee_folder(employee):
    """安全地删除员工的文件夹及其所有内容"""
    try:
        folder_path = get_employee_folder(employee)
        if os.path.exists(folder_path) and os.path.isdir(folder_path):
            shutil.rmtree(folder_path)
            return True
    except Exception as e:
        print(f"删除员工文件夹时出错: {e}")
    return False

def move_employee_files(old_folder, new_folder):
    """安全地移动员工文件到新文件夹"""
    if not old_folder or not new_folder or old_folder == new_folder:
        return True
    
    try:
        if os.path.exists(old_folder) and os.path.isdir(old_folder):
            # 确保目标文件夹存在
            os.makedirs(new_folder, exist_ok=True)
            
            # 移动所有文件
            for filename in os.listdir(old_folder):
                old_path = os.path.join(old_folder, filename)
                new_path = os.path.join(new_folder, filename)
                
                # 如果目标文件已存在，先删除
                if os.path.exists(new_path):
                    os.remove(new_path)
                
                shutil.move(old_path, new_path)
            
            # 删除空的源文件夹
            if os.path.exists(old_folder) and not os.listdir(old_folder):
                os.rmdir(old_folder)
            
            return True
    except Exception as e:
        print(f"移动文件时出错: {e}")
        return False

# 初始化数据库表结构
def init_db():
    with get_db_connection() as db:
        # 创建员工表
        db.table_add('employees')
        
        # 创建离职员工表
        db.table_add('resigned_employees')
        
        # 添加必要的列
        columns = {
            'name': 'TEXT',
            'id_card': 'TEXT',
            'phone': 'TEXT',
            'department': 'TEXT',
            'position': 'TEXT',
            'hire_date': 'TEXT',
            'id_card_front': 'TEXT',  # 身份证正面照片路径
            'id_card_back': 'TEXT',   # 身份证反面照片路径
            'diploma': 'TEXT',        # 毕业证照片路径
            'degree': 'TEXT',         # 学位证照片路径
            'other_certificates': 'TEXT',  # 其他证书，JSON格式存储
            'resign_date': 'TEXT'  # 新增离职日期字段
        }
        
        db.column_add('employees', columns)
        db.column_add('resigned_employees', columns)
        
    return True

# 获取所有员工
def get_all_employees():
    with get_db_connection() as db:
        return db.record_search('employees')

# 根据ID获取员工
def get_employee_by_id(employee_id):
    with get_db_connection() as db:
        result = db.record_search('employees', condition=f'id = {employee_id}')
        if result and len(result) > 0:
            # 处理证件信息
            return process_employee_certificates(result[0])
        return None

# 添加员工
def add_employee(name, id_card, phone, department, position):
    with get_db_connection() as db:
        # 检查身份证号是否已存在
        id_card_check = db.record_search('employees', condition=f'id_card = "{id_card}"')
        if id_card_check and len(id_card_check) > 0:
            return False, "身份证号已存在"
        
        # 获取当前时间（上海时区）
        now = datetime.utcnow().replace(tzinfo=pytz.utc).astimezone(pytz.timezone('Asia/Shanghai'))
        hire_date = now.strftime('%Y-%m-%d')  # 修改为只保留年月日
        
        # 创建员工记录
        employee_data = {
            'name': name,
            'id_card': id_card,
            'phone': phone,
            'department': department,
            'position': position,
            'hire_date': hire_date
        }
        
        try:
            # 添加员工记录到数据库
            db.record_add('employees', employee_data)
            
            # 查询刚添加的员工记录，获取ID
            new_employee = db.record_search('employees', condition=f'id_card = "{id_card}"')
            if new_employee and len(new_employee) > 0:
                # 创建员工文件夹
                get_employee_folder(new_employee[0])
            
            return True, "员工添加成功"
        except Exception as e:
            return False, str(e)

# 更新员工信息
def update_employee(employee_id, data):
    with get_db_connection() as db:
        # 检查员工是否存在
        employee = get_employee_by_id(employee_id)
        if not employee:
            return False, "员工不存在"
        
        # 获取更新前的文件夹路径
        old_folder = get_employee_folder(employee)
        
        # 检查身份证号是否与其他员工冲突
        if 'id_card' in data:
            id_card_check = db.record_search('employees', condition=f'id_card = "{data["id_card"]}" AND id != {employee_id}')
            if id_card_check and len(id_card_check) > 0:
                return False, "身份证号已被其他员工使用"
        
        try:
            # 更新数据库记录
            db.record_update('employees', data, f'id = {employee_id}')
            
            # 获取更新后的员工信息
            updated_employee = get_employee_by_id(employee_id)
            # 获取新的文件夹路径
            new_folder = get_employee_folder(updated_employee)
            
            # 如果文件夹路径发生变化，移动文件
            if old_folder != new_folder:
                move_employee_files(old_folder, new_folder)
            
            return True, "员工信息更新成功"
        except Exception as e:
            return False, str(e)

# 删除员工
def delete_employee(employee_id):
    """删除员工（移至离职员工）"""
    with get_db_connection() as db:
        try:
            # 获取员工信息
            employee = get_employee_by_id(employee_id)
            if not employee:
                return False, "员工不存在"
            
            # 获取当前时间作为离职时间
            now = datetime.utcnow().replace(tzinfo=pytz.utc).astimezone(pytz.timezone('Asia/Shanghai'))
            resign_date = now.strftime('%Y-%m-%d')  # 修改为只保留年月日
            
            # 添加离职日期
            employee['resign_date'] = resign_date
            
            # 获取原文件夹路径
            old_folder = get_employee_folder(employee)
            print(f"原文件夹路径: {old_folder}")  # 调试信息
            
            # 获取新的离职员工文件夹路径和文件夹名
            new_folder = get_resigned_employee_folder(employee)
            folder_name = os.path.basename(new_folder)  # 获取文件夹名
            print(f"新文件夹路径: {new_folder}")  # 调试信息
            print(f"文件夹名: {folder_name}")  # 调试信息
            
            try:
                # 更新文件路径
                if employee.get('id_card_front'):
                    old_name = os.path.basename(employee['id_card_front'])
                    employee['id_card_front'] = f"resigned_employees/{folder_name}/{old_name}"
                if employee.get('id_card_back'):
                    old_name = os.path.basename(employee['id_card_back'])
                    employee['id_card_back'] = f"resigned_employees/{folder_name}/{old_name}"
                if employee.get('diploma'):
                    old_name = os.path.basename(employee['diploma'])
                    employee['diploma'] = f"resigned_employees/{folder_name}/{old_name}"
                if employee.get('degree'):
                    old_name = os.path.basename(employee['degree'])
                    employee['degree'] = f"resigned_employees/{folder_name}/{old_name}"
                
                # 处理其他证书
                if employee.get('other_certificates'):
                    if isinstance(employee['other_certificates'], str):
                        other_certs = json.loads(employee['other_certificates'])
                    else:
                        other_certs = employee['other_certificates']
                    
                    for cert in other_certs:
                        if cert.get('image'):
                            old_name = os.path.basename(cert['image'])
                            cert['image'] = f"resigned_employees/{folder_name}/{old_name}"
                    
                    employee['other_certificates'] = json.dumps(other_certs)
                
                # 将员工记录插入离职员工表
                db.record_add('resigned_employees', employee)
                print("已添加到离职员工表")  # 调试信息
                
                # 从在职员工表中删除
                db.record_delete('employees', f'id = {employee_id}')
                print("已从在职员工表中删除")  # 调试信息
                
                # 移动文件到离职员工文件夹
                if os.path.exists(old_folder):
                    print(f"开始移动文件从 {old_folder} 到 {new_folder}")  # 调试信息
                    if not os.path.exists(new_folder):
                        os.makedirs(new_folder)
                    
                    # 移动所有文件
                    for filename in os.listdir(old_folder):
                        old_path = os.path.join(old_folder, filename)
                        new_path = os.path.join(new_folder, filename)
                        print(f"移动文件: {old_path} -> {new_path}")  # 调试信息
                        shutil.move(old_path, new_path)
                    
                    # 删除原文件夹
                    if os.path.exists(old_folder) and not os.listdir(old_folder):
                        os.rmdir(old_folder)
                        print(f"已删除空文件夹: {old_folder}")  # 调试信息
                
                return True, "已移至离职名单"
            except Exception as e:
                print(f"处理数据库或文件时出错: {e}")
                return False, str(e)
                
        except Exception as e:
            print(f"处理离职员工时出错: {e}")
            return False, str(e)

# 搜索员工
def search_employees(search_term):
    with get_db_connection() as db:
        condition = f"""
            name LIKE '%{search_term}%' OR 
            id_card LIKE '%{search_term}%' OR 
            phone LIKE '%{search_term}%' OR 
            department LIKE '%{search_term}%' OR 
            position LIKE '%{search_term}%'
        """
        return db.record_search('employees', condition=condition)

# 处理员工详情中的证件信息
def process_employee_certificates(employee):
    """处理员工详情中的证件信息，将JSON字符串转换为Python对象"""
    if employee and 'other_certificates' in employee and employee['other_certificates']:
        try:
            if isinstance(employee['other_certificates'], str):
                employee['other_certificates'] = json.loads(employee['other_certificates'])
        except json.JSONDecodeError:
            employee['other_certificates'] = []
    else:
        employee['other_certificates'] = []
    
    return employee

# 更新员工证件信息
def update_employee_certificates(employee_id, certificate_data):
    """更新员工证件信息"""
    with get_db_connection() as db:
        try:
            # 获取当前员工信息
            employee = get_employee_by_id(employee_id)
            if not employee:
                return False, "员工不存在"
            
            # 准备更新数据
            update_data = {}
            
            # 处理主要证件
            for field in ['id_card_front', 'id_card_back', 'diploma', 'degree']:
                if field in certificate_data:
                    update_data[field] = certificate_data[field]
            
            # 处理其他证书
            if 'other_certificates' in certificate_data:
                # 将其他证书列表转换为JSON字符串
                update_data['other_certificates'] = json.dumps(certificate_data['other_certificates'], ensure_ascii=False)
            
            # 更新数据库
            if update_data:
                db.record_update('employees', update_data, f'id = {employee_id}')
                return True, "证件信息更新成功"
            else:
                return False, "没有需要更新的证件信息"
                
        except Exception as e:
            print(f"更新证件信息时出错：{str(e)}")
            return False, str(e)

def get_resigned_employee_folder(employee):
    """获取离职员工文件夹路径"""
    try:
        # 处理入职日期和离职日期
        hire_date = employee.get('hire_date', '')
        resign_date = employee.get('resign_date', '')
        
        if hire_date:
            try:
                # 直接使用 hire_date，因为它已经是 YYYY-MM-DD 格式
                hire_date_str = hire_date.replace('-', '')  # 将 YYYY-MM-DD 转换为 YYYYMMDD
            except:
                hire_date_str = 'unknown_date'
        else:
            hire_date_str = 'unknown_date'

        # 安全地处理文件名组件
        name = sanitize_filename(employee.get('name', 'unknown'))
        position = sanitize_filename(employee.get('position', 'unknown'))
        
        # 创建文件夹名（保持与原始文件夹名一致）
        folder_name = f"{hire_date_str}_{name}_{position}"
        folder_path = os.path.join(RESIGNED_EMPLOYEES_DIR, folder_name)
        
        # 确保文件夹存在
        os.makedirs(folder_path, exist_ok=True)
        
        return folder_path
    except Exception as e:
        print(f"创建离职员工文件夹时出错: {e}")
        backup_folder = os.path.join(RESIGNED_EMPLOYEES_DIR, f"resigned_employee_{employee.get('id', 'unknown')}")
        os.makedirs(backup_folder, exist_ok=True)
        return backup_folder

def get_all_resigned_employees():
    """获取所有离职员工"""
    with get_db_connection() as db:
        return db.record_search('resigned_employees')

def get_resigned_employee_by_id(employee_id):
    """根据ID获取离职员工信息"""
    with get_db_connection() as db:
        result = db.record_search('resigned_employees', condition=f'id = {employee_id}')
        if result and len(result) > 0:
            return process_employee_certificates(result[0])
        return None

def delete_resigned_employee(employee_id):
    """永久删除离职员工信息"""
    with get_db_connection() as db:
        try:
            # 获取离职员工信息
            employee = get_resigned_employee_by_id(employee_id)
            if not employee:
                return False, "离职员工不存在"
            
            # 获取员工文件夹路径
            folder_path = get_resigned_employee_folder(employee)
            print(f"准备删除文件夹: {folder_path}")  # 调试信息
            
            try:
                # 从数据库中删除记录
                db.record_delete('resigned_employees', f'id = {employee_id}')
                print("已从数据库中删除记录")  # 调试信息
                
                # 删除文件夹及其内容
                if os.path.exists(folder_path):
                    print(f"删除文件夹及其内容: {folder_path}")  # 调试信息
                    shutil.rmtree(folder_path)
                
                return True, "离职员工信息已永久删除"
            except Exception as e:
                print(f"删除过程中出错: {e}")
                return False, str(e)
                
        except Exception as e:
            print(f"删除离职员工时出错: {e}")
            return False, str(e)

# 如果直接运行此脚本，则初始化数据库
if __name__ == "__main__":
    init_db()
    print("数据库管理器已准备就绪")
    
    # 可以添加一些测试数据
    add_employee("张三", "110101199001011234", "13800138000", "技术部", "工程师")
    add_employee("李四", "110101199102022345", "13900139000", "市场部", "经理")
    
    print("测试数据已添加")
    
    # 显示所有员工
    employees = get_all_employees()
    for emp in employees:
        print(f"ID: {emp['id']}, 姓名: {emp['name']}, 部门: {emp['department']}, 职位: {emp['position']}") 